# -*- coding: UTF-8  -*-
import pgdb
"""
用于数据库操作的相关方法。
"""

def getconn(cfg):
    '''
    获取数据库连接。
    使用方法：
    cfg = {"host":"localhost", "user":"username", "passwd":"***", "database":"dbname"}
    conn = getconn(cfg)
    '''
    conn = None
    try:
        host = cfg["host"]
        user = cfg["user"]
        pswd = cfg["passwd"]
        db= cfg["db"]
        conn = pgdb.connect(host=host, database=db, user=user, password=pswd)
    except Exception, msg:
        print "db getconn failed.", msg
    return conn;


def submit(conn):
    '''
    提交任务，并关闭数据库连接。
    '''
    try:
        conn.commit()
    except Exception, msg:
        print "db submit failed.", msg
    finally:
        close(conn)


def create(conn, tb, obj):
	_save(conn, tb, obj, False)


def save(conn, tb, obj):
	_save(conn,tb, obj, True);


def _save(conn, tb, obj, saveOrCreate):
    '''
    保存/添加一行记录。
    '''
    tableName = tb['name']
    fields = tb["fields"].split(' ')
    formats = tb['format']
    formats_sql = '';
    fields_sql = '';
    defaults = tb['defaults']
    field_fmt_sql = '';

    for p in range(0,len(formats)):

        format = formats[p];
        format = "%"+format;
        formats_sql = formats_sql + format;
        field = fields[p];
        fields_sql = fields_sql + field;

        if saveOrCreate:
            field = field + "=" + format;
            field_fmt_sql = field_fmt_sql + field;

        if p < len(formats)-1 :
            formats_sql = formats_sql + ',';
            fields_sql = fields_sql + ',';
            if saveOrCreate:
                field_fmt_sql = field_fmt_sql + ',';

    if saveOrCreate:
        SQL = '''UPDATE %s SET  %s WHERE %s
''' % (tableName, field_fmt_sql, fields[0]+"=%"+formats[0])
    else:
        SQL = '''INSERT INTO %s (%s) VALUES (%s)
''' % (tableName, fields_sql, formats_sql)

    r = conn.cursor();
    r.execute("SELECT nextval('%s')" % tb['idseq'])
    ID = r.fetchone()[0];

    values = [];
    pos = 0;
    exist_id_value = 0;

    for f in fields:

        if(f == 'id' and not saveOrCreate):
            values.append(ID);
        else:
            value = '';
            try:
                value = getattr(obj, f);
                if f == 'id':
                    exist_id_value = value;
            except AttributeError:
                    value = defaults[pos]

            values.append(value)

        pos = pos+1;

    if saveOrCreate:
        values.append(exist_id_value);

    r.execute(SQL, values)
	# create EOF


def query(conn="", SQL="", params=[], fields="", replaceNone=True):

    r = conn.cursor();
    r.execute(SQL, params)
    rows = r.fetchall();
    r.close();

    fs = fields.split(' ');
    list = [];

    for row in rows:
        class Obj(): pass;
        obj = Obj();
        pos = 0;
        for f in row:
            if replaceNone and f==None:
                f = ''
            setattr(obj, fs[pos], f)
            pos = pos+1;
        list.append(obj)

    return list;


def read(conn, tb, ID, replaceNone=True):
    '''
    读取指定ID的行。
    '''
    tableName = tb['name']
    fields = tb["fields"].split(' ')
    fields_sql = '';
    formats = tb['format']

    for p in range(0,len(formats)):
        field = fields[p];
        fields_sql = fields_sql + field;
        if p < len(formats)-1 :
            fields_sql = fields_sql + ',';

    SQL = '''SELECT %s FROM %s WHERE %s
''' % ( fields_sql, tableName, fields[0]+"=%"+formats[0] )

    r = conn.cursor();
    r.execute(SQL, [int(ID)])
    row = r.fetchone();
    class Obj():pass;
    obj = Obj();

    if row == None:
        obj.__isNull__ = True;
        return obj;
    pos = 0;
    for f in fields:
        if replaceNone and row[pos] == None:
            row[pos] = ''
        setattr(obj, f, row[pos])
        pos = pos + 1;

    r.close()
    return obj;


def delete(conn, tb, ID):
    '''
    删除指定ID的行。
    '''
    fields = tb["fields"].split(' ')
    formats = tb['format']
    tableName = tb['name']

    SQL = '''DELETE FROM %s WHERE %s
''' % (tableName, fields[0]+"=%"+formats[0])

    r = conn.cursor();
    r.execute(SQL, [int(ID)])
    r.close();


def close(conn):
    '''
    关闭数据库连接。
    '''
    try:
        conn.close()
    except Exception, msg:
        print "db close failed.", msg
